USE [master]
GO
/****** Object:  Database [CS333]    Script Date: 4/30/2013 8:48:55 PM ******/
CREATE DATABASE [CS333]
GO
USE [CS333]
GO
/****** Object:  StoredProcedure [dbo].[AdminLogin]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AdminLogin](
	@username nvarchar(50),
	@password varchar(64)
)
AS
	IF EXISTS(SELECT username 
	FROM users
	WHERE @username = username and @password = [password] and admin = 1 and active = 1)
	BEGIN 
		SELECT 'TRUE'
	END
	ELSE 
	BEGIN 
		SELECT 'FALSE'
	END
GO
/****** Object:  StoredProcedure [dbo].[ChatLogin]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChatLogin]

	@username nvarchar(50) OUTPUT,

	@password varchar(64) OUTPUT

AS

BEGIN

	IF EXISTS (SELECT username

				FROM CS333.dbo.users

				WHERE @username = username AND @password = [password])

		BEGIN

			SELECT 'TRUE'

		END

	ELSE

		BEGIN

			SELECT 'FALSE'

		END

END
GO
/****** Object:  StoredProcedure [dbo].[deleteUser]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================
 
CREATE PROCEDURE [dbo].[deleteUser] 

-- Add the parameters for the stored procedure here
 
@ID int,
 
@count int OUTPUT
 
 
AS

BEGIN
 
-- SET NOCOUNT ON added to prevent extra result sets from
 
-- interfering with SELECT statements.
 
SET NOCOUNT OFF;
 
-- Insert statements for procedure here
 
DELETE FROM users

WHERE userId = @ID

SELECT @count = @@ROWCOUNT 

return @count


 
END

GO
/****** Object:  StoredProcedure [dbo].[NewUser]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NewUser]
	@username nvarchar(50),
	@admin bit, 
	@active bit,
	@password varchar(64)
	
AS
BEGIN
DECLARE @user_id as INT

INSERT INTO users(username, [admin], active, [password])
VALUES (@username, @admin, @active, @password)
SELECT @user_id = CAST(SCOPE_IDENTITY() AS INT)
RETURN @user_id
END
GO
/****** Object:  StoredProcedure [dbo].[userUpdate]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[userUpdate](
@username nvarchar(50),
@admin bit,
@active bit,
@password varchar(64),
@ID int,
@count int OUTPUT
)
AS
BEGIN
IF EXISTS (SELECT @username FROM users WHERE @username = @username)
UPDATE users
SET @username = username, @admin= [admin], @active = active, @password = [password]
WHERE userId = @ID
SELECT @count = @@ROWCOUNT
return @count

END

GO
/****** Object:  Table [dbo].[History]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[History](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fkroomId] [int] NOT NULL,
	[fkuserId] [int] NOT NULL,
	[timeStamp] [datetime] NOT NULL,
	[messge] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[roomPermisions]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[roomPermisions](
	[permId] [int] IDENTITY(1,1) NOT NULL,
	[fkRoomId] [int] NULL,
	[fkUserId] [int] NULL,
 CONSTRAINT [PK_roomPermisions] PRIMARY KEY CLUSTERED 
(
	[permId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Rooms]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rooms](
	[roomId] [int] IDENTITY(1,1) NOT NULL,
	[roomName] [nchar](10) NULL,
 CONSTRAINT [PK_Rooms] PRIMARY KEY CLUSTERED 
(
	[roomId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[users]    Script Date: 4/30/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
	[userId] [int] IDENTITY(1,1) NOT NULL,
	[username] [nvarchar](50) NOT NULL,
	[admin] [bit] NULL,
	[active] [bit] NULL,
	[password] [varchar](64) NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_admin]  DEFAULT ((0)) FOR [admin]
GO
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_active]  DEFAULT ((1)) FOR [active]
GO
ALTER TABLE [dbo].[History]  WITH CHECK ADD  CONSTRAINT [FK_History_Rooms] FOREIGN KEY([fkroomId])
REFERENCES [dbo].[Rooms] ([roomId])
GO
ALTER TABLE [dbo].[History] CHECK CONSTRAINT [FK_History_Rooms]
GO
ALTER TABLE [dbo].[History]  WITH CHECK ADD  CONSTRAINT [FK_History_users] FOREIGN KEY([fkuserId])
REFERENCES [dbo].[users] ([userId])
GO
ALTER TABLE [dbo].[History] CHECK CONSTRAINT [FK_History_users]
GO
ALTER TABLE [dbo].[roomPermisions]  WITH CHECK ADD  CONSTRAINT [FK_roomPermisions_Rooms] FOREIGN KEY([fkRoomId])
REFERENCES [dbo].[Rooms] ([roomId])
GO
ALTER TABLE [dbo].[roomPermisions] CHECK CONSTRAINT [FK_roomPermisions_Rooms]
GO
ALTER TABLE [dbo].[roomPermisions]  WITH CHECK ADD  CONSTRAINT [FK_roomPermisions_users] FOREIGN KEY([fkUserId])
REFERENCES [dbo].[users] ([userId])
GO
ALTER TABLE [dbo].[roomPermisions] CHECK CONSTRAINT [FK_roomPermisions_users]
GO
USE [master]
GO
ALTER DATABASE [CS333] SET  READ_WRITE 
GO
